const ExcelJS = require('exceljs');
const Cadre = require('../models/Cadre');
const Unit = require('../models/Unit');
const University = require('../models/University');
const Plan = require('../models/Plan');

// 枚举值映射表
const mappings = {
  gender: {
    '男': '男',
    '女': '女',
    '其他': '其他'
  },
  level: {
    '省级': '省级',
    '厅局级': '厅局级',
    '县处级': '县处级',
    '乡科级': '乡科级',
    '其他': '其他'
  },
  cadreType: {
    '厅局级干部': '厅局级干部',
    '中管干部': '中管干部'
  },
  education: {
    '博士研究生': '博士研究生',
    '硕士研究生': '硕士研究生',
    '本科': '本科',
    '大专': '大专',
    '高中': '高中',
    '其他': '其他'
  },
  degree: {
    '博士': '博士',
    '硕士': '硕士',
    '学士': '学士',
    '无': '无'
  },
  status: {
    'active': '在职',
    'seconded': '借调',
    'retired': '退休'
  },
  planStatus: {
    'planned': '计划中',
    'ongoing': '进行中',
    'completed': '已完成',
    'cancelled': '已取消'
  }
};

// 格式化日期
const formatDate = (date) => {
  if (!date) return '';
  const d = new Date(date);
  return `${d.getFullYear()}-${String(d.getMonth() + 1).padStart(2, '0')}-${String(d.getDate()).padStart(2, '0')}`;
};

// 格式化时间戳为年月
const formatBirthDate = (timestamp) => {
  if (!timestamp) return '';
  const date = new Date(timestamp);
  return `${date.getFullYear()}-${String(date.getMonth() + 1).padStart(2, '0')}`;
};

// 导出干部信息
const exportCadres = async (req, res) => {
  try {
    // 查询所有干部信息并关联单位和高校
    const cadres = await Cadre.find()
      .populate('unitId', 'name')
      .populate('intendedUniversity1', 'schoolName')
      .populate('intendedUniversity2', 'schoolName')
      .populate('intendedUniversity3', 'schoolName')
      .lean();

    // 创建工作簿
    const workbook = new ExcelJS.Workbook();
    const worksheet = workbook.addWorksheet('干部信息');

    // 设置列定义
    worksheet.columns = [
      { header: '姓名', key: 'name', width: 15 },
      { header: '性别', key: 'gender', width: 10 },
      { header: '出生年月', key: 'birthDate', width: 15 },
      { header: '级别', key: 'level', width: 15 },
      { header: '干部类型', key: 'cadreType', width: 15 },
      { header: '所属单位', key: 'unit', width: 25 },
      { header: '最高学历', key: 'education', width: 15 },
      { header: '最高学位', key: 'degree', width: 15 },
      { header: '拟授课方向', key: 'teachingDirections', width: 40 },
      { header: '意向高校1', key: 'intendedUniversity1', width: 25 },
      { header: '意向高校2', key: 'intendedUniversity2', width: 25 },
      { header: '意向高校3', key: 'intendedUniversity3', width: 25 },
      { header: '人事变动', key: 'personnelChanges', width: 30 },
      { header: '备注', key: 'remarks', width: 30 }
    ];

    // 设置表头样式
    worksheet.getRow(1).font = { bold: true };
    worksheet.getRow(1).fill = {
      type: 'pattern',
      pattern: 'solid',
      fgColor: { argb: 'FFE6F3FF' }
    };

    // 添加数据
    cadres.forEach(cadre => {
      worksheet.addRow({
        name: cadre.name || '',
        gender: mappings.gender[cadre.gender] || cadre.gender || '',
        birthDate: formatBirthDate(cadre.birthDate),
        level: mappings.level[cadre.level] || cadre.level || '',
        cadreType: mappings.cadreType[cadre.cadreType] || cadre.cadreType || '',
        unit: cadre.unitId?.name || '',
        education: mappings.education[cadre.highestEducation] || cadre.highestEducation || '',
        degree: mappings.degree[cadre.highestDegree] || cadre.highestDegree || '',
        teachingDirections: Array.isArray(cadre.proposedTeachingDirections) ? cadre.proposedTeachingDirections.join('、') : '',
        intendedUniversity1: cadre.intendedUniversity1?.schoolName || '',
        intendedUniversity2: cadre.intendedUniversity2?.schoolName || '',
        intendedUniversity3: cadre.intendedUniversity3?.schoolName || '',
        personnelChanges: cadre.personnelChanges || '',
        remarks: cadre.remarks || ''
      });
    });

    // 设置响应头
    res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    res.setHeader('Content-Disposition', `attachment; filename="cadres_${new Date().toISOString().split('T')[0]}.xlsx"`);

    // 写入响应
    await workbook.xlsx.write(res);
    res.end();

  } catch (error) {
    console.error('导出干部信息失败:', error);
    res.status(500).json({ error: '导出失败', details: error.message });
  }
};

// 导出单位信息
const exportUnits = async (req, res) => {
  try {
    const units = await Unit.find().lean();

    const workbook = new ExcelJS.Workbook();
    const worksheet = workbook.addWorksheet('单位信息');

    worksheet.columns = [
      { header: '单位名称', key: 'name', width: 30 },
      { header: '单位简称', key: 'shortName', width: 20 },
      { header: '标定代码', key: 'calibrationCode', width: 15 },
      { header: '回执代码', key: 'receiptCode', width: 15 },
      { header: '单位类型', key: 'unitType', width: 20 },
      { header: '单位级别', key: 'unitLevel', width: 15 },
      { header: '联系人', key: 'contactPerson', width: 15 },
      { header: '座机电话', key: 'landlinePhone', width: 20 },
      { header: '手机号码', key: 'mobilePhone', width: 20 },
      { header: '备注', key: 'remarks', width: 40 }
    ];

    // 设置表头样式
    worksheet.getRow(1).font = { bold: true };
    worksheet.getRow(1).fill = {
      type: 'pattern',
      pattern: 'solid',
      fgColor: { argb: 'FFE6F3FF' }
    };

    units.forEach(unit => {
      worksheet.addRow({
        name: unit.name || '',
        shortName: unit.shortName || '',
        calibrationCode: unit.calibrationCode || '',
        receiptCode: unit.receiptCode || '',
        unitType: unit.unitType || '',
        unitLevel: unit.unitLevel || '',
        contactPerson: unit.contactPerson || '',
        landlinePhone: unit.landlinePhone || '',
        mobilePhone: unit.mobilePhone || '',
        remarks: unit.remarks || ''
      });
    });

    res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    res.setHeader('Content-Disposition', `attachment; filename="units_${new Date().toISOString().split('T')[0]}.xlsx"`);

    await workbook.xlsx.write(res);
    res.end();

  } catch (error) {
    console.error('导出单位信息失败:', error);
    res.status(500).json({ error: '导出失败', details: error.message });
  }
};

// 导出高校信息
const exportUniversities = async (req, res) => {
  try {
    const universities = await University.find().lean();

    const workbook = new ExcelJS.Workbook();
    const worksheet = workbook.addWorksheet('高校信息');

    worksheet.columns = [
      { header: '学校名称', key: 'schoolName', width: 30 },
      { header: '学校标准码', key: 'schoolCode', width: 20 },
      { header: '主管部门', key: 'governingDepartment', width: 20 },
      { header: '所在地区', key: 'location', width: 15 },
      { header: '是否中央直属', key: 'isCentral', width: 15 },
      { header: '联系部门', key: 'contactDepartment', width: 20 },
      { header: '联系人职务', key: 'contactPosition', width: 15 },
      { header: '联系人姓名', key: 'contactName', width: 15 },
      { header: '座机电话', key: 'landlinePhone', width: 20 },
      { header: '手机号码', key: 'mobilePhone', width: 20 },
      { header: '备注', key: 'remarks', width: 40 }
    ];

    // 设置表头样式
    worksheet.getRow(1).font = { bold: true };
    worksheet.getRow(1).fill = {
      type: 'pattern',
      pattern: 'solid',
      fgColor: { argb: 'FFE6F3FF' }
    };

    universities.forEach(university => {
      worksheet.addRow({
        schoolName: university.schoolName || '',
        schoolCode: university.schoolCode || '',
        governingDepartment: university.governingDepartment || '',
        location: university.location || '',
        isCentral: university.isCentral ? '是' : '否',
        contactDepartment: university.contactDepartment || '',
        contactPosition: university.contactPosition || '',
        contactName: university.contactName || '',
        landlinePhone: university.landlinePhone || '',
        mobilePhone: university.mobilePhone || '',
        remarks: university.remarks || ''
      });
    });

    res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    res.setHeader('Content-Disposition', `attachment; filename="universities_${new Date().toISOString().split('T')[0]}.xlsx"`);

    await workbook.xlsx.write(res);
    res.end();

  } catch (error) {
    console.error('导出高校信息失败:', error);
    res.status(500).json({ error: '导出失败', details: error.message });
  }
};

// 导出授课计划信息
const exportPlans = async (req, res) => {
  try {
    const plans = await Plan.find()
      .populate('cadreId', 'name')
      .populate('universityId', 'schoolName')
      .lean();

    const workbook = new ExcelJS.Workbook();
    const worksheet = workbook.addWorksheet('授课计划');

    worksheet.columns = [
      { header: '授课干部', key: 'cadreName', width: 15 },
      { header: '授课高校', key: 'universityName', width: 25 },
      { header: '课程系列', key: 'courseSeries', width: 25 },
      { header: '课程名称', key: 'courseName', width: 25 },
      { header: '授课日期', key: 'classDate', width: 15 },
      { header: '授课时间', key: 'classTime', width: 15 },
      { header: '授课地点', key: 'location', width: 25 },
      { header: '计划学时', key: 'plannedHours', width: 15 },
      { header: '授课状态', key: 'teachingStatus', width: 12 },
      { header: '授课学期', key: 'teachingSemester', width: 20 },
      { header: '备注', key: 'remarks', width: 30 }
    ];

    // 设置表头样式
    worksheet.getRow(1).font = { bold: true };
    worksheet.getRow(1).fill = {
      type: 'pattern',
      pattern: 'solid',
      fgColor: { argb: 'FFE6F3FF' }
    };

    plans.forEach(plan => {
      worksheet.addRow({
        cadreName: plan.cadreId?.name || '',
        universityName: plan.universityId?.schoolName || '',
        courseSeries: plan.courseSeries || '',
        courseName: plan.courseName || '',
        classDate: formatDate(plan.classDate),
        classTime: plan.classTime || '',
        location: plan.location || '',
        plannedHours: plan.plannedHours || '',
        teachingStatus: plan.teachingStatus || '',
        teachingSemester: plan.teachingSemester || '',
        remarks: plan.remarks || ''
      });
    });

    res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    res.setHeader('Content-Disposition', `attachment; filename="plans_${new Date().toISOString().split('T')[0]}.xlsx"`);

    await workbook.xlsx.write(res);
    res.end();

  } catch (error) {
    console.error('导出授课计划失败:', error);
    res.status(500).json({ error: '导出失败', details: error.message });
  }
};

module.exports = {
  exportCadres,
  exportUnits,
  exportUniversities,
  exportPlans
};
